package dataLayer;

import interfaces.IDAOAbsence;

import java.sql.Date;
import java.sql.ResultSet;
import java.sql.Statement;
import java.time.LocalDate;
import java.util.TreeSet;

import controller.Dayofweek;
import model.Absence;
import model.Teacher;

public class DAOAbsence extends DBconnect implements IDAOAbsence {

	private static DAOAbsence single;

	TreeSet<Absence> absenceset = new TreeSet<Absence>();

	// Make SQL Query and call to DB to addAbsence
	public void InsertAbsence(Absence a) {
		try {
			openConn();
			Statement stmt = getConn().createStatement();
			Date d = new Date(a.getAbsence_StartDate().getTime());

			Date e = new Date(a.getAbsence_EndDate().getTime());

			String sql = "call sp_add_absence('" + a.getAbsence_Id() + "', '"
					+ a.getAbsence_Teacher() + "', '"
					+ a.getAbsence_Substitude() + "', '" + a.getAbsence_Hours()
					+ "', '" + a.getAbsence_Note() + "', '" + d + "', '" + e
					+ "', '" + a.getAbsence_Reason() + "')";

			stmt.execute(sql);
			stmt.close();
			closeConn();

		} catch (Exception e) {
			System.err.println("SQL Error DAO insertAbsence");

		}
	}

	@Override
	public void loadAbsence(Absence a) {
		// TODO Auto-generated method stub

	}
	//Make SQL query and call to DB to LoadabsencesDay 
	public TreeSet<Absence> loadAllAbsencesday(java.util.Date date) {
		try {
			System.out.println(date);
			openConn();
			Statement stmt = getConn().createStatement();
			String sql = "call sp_load_absences_day('"
					+ date + "')";
			System.out.println(sql);
			ResultSet rs = stmt.executeQuery(sql);
			absenceset.clear();
			while (rs.next()){
				Absence a = new Absence(0, null, null, 0, null, null, null,
						null);

				a.setAbsence_Id(rs.getInt("id"));
				a.setAbsence_Teacher(rs.getString("teacher"));
				a.setAbsence_Substitude(rs.getString("substitute"));
				a.setAbsence_Hours(rs.getInt("hours"));
				a.setAbsence_Note(rs.getString("note"));
				a.setAbsence_StartDate(rs.getDate("time_start"));
				a.setAbsence_EndDate(rs.getDate("time_end"));
				a.setAbsence_Reason(rs.getString("reason"));

				absenceset.add(a);

			}
			stmt.close();
			closeConn();
		} catch (Exception e) {
			e.printStackTrace();
			System.err.println("SQL Error Dao loadAllAbsencesDay");
		}
		absenceset.size();
		return absenceset;
	}
	
	// Make SQL query and call to DB to loadAbsenceFromTeacher
	public TreeSet<Absence> loadAbsenceFromTeacher(Teacher t) {

		// t = PanelTeacherDetails.getInstance().getCurrentTeacher();
		try {
			openConn();
			Statement stmt = getConn().createStatement();
			String sql = "call sp_load_all_absences_from_teacher('"
					+ t.getTeacher_code() + "')";
			ResultSet rs = stmt.executeQuery(sql);
			absenceset.clear();

			while (rs.next()) {

				Absence a = new Absence(0, null, null, 0, null, null, null,
						null);

				a.setAbsence_Id(rs.getInt("id"));
				a.setAbsence_Teacher(rs.getString("teacher"));
				a.setAbsence_Substitude(rs.getString("substitute"));
				a.setAbsence_Hours(rs.getInt("hours"));
				a.setAbsence_Note(rs.getString("note"));
				a.setAbsence_StartDate(rs.getDate("time_start"));
				a.setAbsence_EndDate(rs.getDate("time_end"));
				a.setAbsence_Reason(rs.getString("reason"));

				absenceset.add(a);

			}

			stmt.close();
			closeConn();
		} catch (Exception e) {
			e.printStackTrace();
			System.err.println("SQL Error Dao LoadAllAbsenceFromTeacher");
		}
		return absenceset;
	}

	// Make SQL query and call to DB to EditAbsence
	public void updateAbsence(Absence a) {
		try {
			openConn();
			Statement stmt = getConn().createStatement();

			Date SD = new Date(a.getAbsence_StartDate().getTime());
			Date ED = new Date(a.getAbsence_EndDate().getTime());

			String sql = "call sp_update_absences('" + a.getAbsence_Id()
					+ "', '" + a.getAbsence_Teacher() + "', '"
					+ a.getAbsence_Substitude() + "', '" + a.getAbsence_Hours()
					+ "', '" + a.getAbsence_Note() + "', '"
					+ a.getAbsence_StartDate().getTime() + "', '" + ED + "', '"
					+ a.getAbsence_Reason() + "')";

			stmt.execute(sql);
			stmt.close();
			closeConn();
		} catch (Exception e) {
			System.err.println("SQL Error Dao UpdateAbsence");

		}

	}

	// Make SQL query and call to DB to deleteAbsence
	public void deleteAbsence(Absence a) {
		try {
			openConn();
			Statement stmt = getConn().createStatement();
			String sql = "CALL `sp_delete_absence`('" + a.getAbsence_Id()
					+ "')";
			stmt.execute(sql);
			stmt.close();
			closeConn();
		} catch (Exception e) {
			System.err.println("SQL Error Dao DeleteAbsence");

		}
	}

	// Make SQL query and call to DB to loadAllAbsences
	public TreeSet<Absence> loadAllAbsence() {
		try {
			openConn();
			Statement stmt = getConn().createStatement();
			String sql = "call sp_load_all_absences";
			ResultSet rs = stmt.executeQuery(sql);
			absenceset.clear();

			while (rs.next()) {

				Absence a = new Absence(0, null, null, 0, null, null, null,
						null);

				a.setAbsence_Id(rs.getInt("id"));
				a.setAbsence_Teacher(rs.getString("teacher"));
				a.setAbsence_Substitude(rs.getString("substitute"));
				a.setAbsence_Hours(rs.getInt("hours"));
				a.setAbsence_Note(rs.getString("note"));
				a.setAbsence_StartDate(rs.getDate("time_start"));
				a.setAbsence_EndDate(rs.getDate("time_end"));
				a.setAbsence_Reason(rs.getString("reason"));

				absenceset.add(a);

			}

			stmt.close();
			closeConn();
		} catch (Exception e) {
			e.printStackTrace();
			System.err.println("SQL Error Dao LoadAllAbsence");
		}
		return absenceset;
	}

	// Make SQL query and call to DB to loadAllAbsencesToday
	public TreeSet<Absence> loadAllAbsenceToday() {

		TreeSet<Absence> absenceset = new TreeSet<Absence>();

		try {
			openConn();
			Statement stmt = getConn().createStatement();
			String sql = "call sp_load_all_absences_today";
			ResultSet rs = stmt.executeQuery(sql);
			absenceset.clear();

			while (rs.next()) {

				Absence a = new Absence(0, null, null, 0, null, null, null,
						null);
				a.setAbsence_Id(rs.getInt("id"));
				a.setAbsence_Teacher(rs.getString("teacher"));
				a.setAbsence_Substitude(rs.getString("substitute"));
				a.setAbsence_Hours(rs.getInt("hours"));
				a.setAbsence_Note(rs.getString("note"));
				a.setAbsence_StartDate(rs.getDate("time_start"));
				a.setAbsence_EndDate(rs.getDate("time_end"));
				a.setAbsence_Reason(rs.getString("reason"));
				absenceset.add(a);
			}

			stmt.close();
			closeConn();
		} catch (Exception e) {

			e.printStackTrace();
			System.err.println("SQL Error Dao LoadAllAbsenceToday");
		}

		return absenceset;
	}

	// Make SQL query and call to DB to loadAllAbsencesWeek
	public TreeSet<Absence> loadAllAbsenceWeek() {
		TreeSet<Absence> absenceset = new TreeSet<Absence>();
		LocalDate monday = Dayofweek.getFirstDayOfWeek();
		LocalDate sunday = Dayofweek.getLastDayOfWeek();
		try {
			openConn();
			Statement stmt = getConn().createStatement();
			String sql = "call sp_load_all_absences_week('" + monday
					+ " 0:00:00', '" + sunday + " 23:59:59')";

			ResultSet rs = stmt.executeQuery(sql);
			absenceset.clear();

			while (rs.next()) {

				Absence a = new Absence(0, null, null, 0, null, null, null,
						null);
				a.setAbsence_Id(rs.getInt("id"));
				a.setAbsence_Teacher(rs.getString("teacher"));
				a.setAbsence_Substitude(rs.getString("substitute"));
				a.setAbsence_Hours(rs.getInt("hours"));
				a.setAbsence_Note(rs.getString("note"));
				a.setAbsence_StartDate(rs.getDate("time_start"));
				a.setAbsence_EndDate(rs.getDate("time_end"));
				a.setAbsence_Reason(rs.getString("reason"));
				absenceset.add(a);
			}

			stmt.close();
			closeConn();
		} catch (Exception e) {

			e.printStackTrace();
			System.err.println("SQL Error Dao LoadAllAbsenceWeek");
		}

		return absenceset;
	}

	// Make SQL query and call to DB to loadAllAbsencesMonth
	public TreeSet<Absence> loadAllAbsenceMonth(int month, int Year) {
		TreeSet<Absence> absenceset = new TreeSet<Absence>();
		String firstDayOfMonth = "01" + month + Year;
		String lastDayOfMonth = Dayofweek.getLastDayOfMonth(month, Year)
				+ month + Year;
		try {
			openConn();
			Statement stmt = getConn().createStatement();
			String sql = "call sp_load_all_absences_month('" + firstDayOfMonth
					+ " 0:00:00', '" + lastDayOfMonth + " 23:59:59')";
			System.out.println(sql);
			ResultSet rs = stmt.executeQuery(sql);
			absenceset.clear();

			while (rs.next()) {

				Absence a = new Absence(0, null, null, 0, null, null, null,
						null);
				a.setAbsence_Id(rs.getInt("id"));
				a.setAbsence_Teacher(rs.getString("teacher"));
				a.setAbsence_Substitude(rs.getString("substitute"));
				a.setAbsence_Hours(rs.getInt("hours"));
				a.setAbsence_Note(rs.getString("note"));
				a.setAbsence_StartDate(rs.getDate("time_start"));
				a.setAbsence_EndDate(rs.getDate("time_end"));
				a.setAbsence_Reason(rs.getString("reason"));
				absenceset.add(a);
			}

			stmt.close();
			closeConn();
		} catch (Exception e) {

			e.printStackTrace();
			System.err.println("SQL Error Dao LoadAllAbsenceMonth");
		}

		return absenceset;
	}

	public static DAOAbsence getInstance() {
		if (single == null) {
			single = new DAOAbsence();
		}
		return single;
	}

	

}
